# -*_ codeing=utf-8 -*-
# @Time: 2023/3/22 15:10
# @Author: foxhuty
# @File: image_to_mysql.py
# @Software: PyCharm

import mysql.connector
import os


def insert_photo(file_path):
    # Connect to the MySQL database
    cnn = mysql.connector.connect(user='root', password='foxmmer',
                                  host='localhost',
                                  database='db_photos')
    # Prepare the SQL statement to insert the photo into the database
    add_sql = ("INSERT INTO images "
               "(name, data) "
               "VALUES (%s, %s)")

    # Open the photo file and read the data
    # file_path = r'D:\爬虫数据\girl-reading.jpg'
    with open(file_path, 'rb') as file:
        photo_data = file.read()
        name = file_path.split('\\')[-1]

    # Prepare the values to insert into the database
    photo_values = (name, photo_data)

    # Execute the SQL command to insert the photo into the database
    cursor = cnn.cursor()
    cursor.execute(add_sql, photo_values)
    cnn.commit()
    # Close the database connection
    cursor.close()
    cnn.close()


def get_image(index):
    # Connect to the MySQL database
    cnn = mysql.connector.connect(user='root', password='foxmmer',
                                  host='localhost',
                                  database='db_photos')
    # read the image
    image_sql = f'select * from images where id ={index}'
    cursor = cnn.cursor()
    cursor.execute(image_sql)
    file_list = cursor.fetchone()
    name = file_list[1]
    data = file_list[2]
    # print(data)
    with open(name, 'wb') as f:
        f.write(data)
    # Close the database connection
    cursor.close()
    cnn.close()


if __name__ == '__main__':
    # print(os.path.abspath(r'D:\爬虫数据\beauty'))
    # print(os.listdir(r'D:\爬虫数据\beauty'))
    # for image in os.listdir(r'D:\lightroomPhotos\photoCollection\2022_trip_to_Guizhou'):
    #     insert_photo(os.path.abspath(r'D:\lightroomPhotos\photoCollection\2022_trip_to_Guizhou')+'\\'+image)
    for i in range(770, 774):
        get_image(i)
